<?php

namespace App\Repositories;

use App\Models\Announcement;
use Illuminate\Support\Facades\DB;

class AnnouncementRepository extends BaseRepository
{
    public function model()
    {
        return Announcement::class;
    }

    /**
     * Date: 2018/4/23
     * @author Tenny
     * @param $uid
     * @param $title
     * @param $time
     * @param $limit
     * @return \Illuminate\Database\Eloquent\Collection|static[]
     */
    public function getTotalAnnouncements($title = false, $time = false, $author = false, $limit = 15)
    {
        $query = \DB::table('announcements as a');
        if ($title !== false)
        {
            $query->where('a.title', 'like', '%' . $title . '%');
        }
        if ($time !== false)
        {
            $start_time = date("Y-m-d 00:00:00", strtotime($time));
            $end_time = date("Y-m-d 23:59:59", strtotime($time));
            $query->where('a.published_at', '>=', $start_time);
            $query->where('a.published_at', '<=', $end_time);
        }
        if ($author !== false)
        {
            $query->where('users.name', 'like', '%' . $author . '%');
        }
        $announcements = $query
            ->select(['a.id', 'a.title', 'users.name', 'a.published_at'])
            ->where('a.type', '=', 0)
            ->leftJoin('users', 'users.id', '=', 'a.user_id')
            ->orderBy('a.published_at', 'desc')
            ->orderBy('a.id', 'asc')
            ->paginate($limit);
        return $announcements;
    }

     /**
     * Date: 2018/4/23
     * @author Tenny
     * @param $uid
     * @param $title
     * @param $time
     * @param $company_id
     * @param $limit
     * @return \Illuminate\Database\Eloquent\Collection|static[]
     */
    public function getUserAnnouncements($uid, $title = false, $time = false, $author = false, $limit = 15)
    {
        $now = date("Y-m-d H:i:s", time());
        $query = \DB::table('user_project_pivot as u');
        $query->where('u.user_id','=', $uid)
            ->where('u.start', '<=', $now)
            ->where('u.end', '>=', $now);
        //用户关联项目
        $query->leftJoin('announcement_project_pivot as ap', function($join){
            $join->on(function($query){
                $query->on(function($query){
                    $query->on( 'ap.project_id', '=', 'u.project_id')
                        ->where('ap.type', '=', '0');//项目
                    })
                    ->orWhere('ap.type', '=', '1');//公司
            });
        });
        //项目关联通知
        $query->leftJoin('announcements as a', 'a.id', '=', 'ap.announcement_id');
        if ($title !== false)
        {
            $query->where('a.title', 'like', '%' . $title . '%');
        }
        if ($time !== false)
        {
            $start_time = date("Y-m-d 00:00:00", strtotime($time));
            $end_time = date("Y-m-d 23:59:59", strtotime($time));
            $query->where('a.published_at', '>=', $start_time);
            $query->where('a.published_at', '<=', $end_time);
        }
        if ($author !== false)
        {
            $query->where('users.name', 'like', '%' . $author . '%');
        }

        //关联是否已读关系
        $query->leftJoin('announcement_user_pivot as r', 'r.announcement_id', '=', 'a.id');

        $announcements = $query
             ->select(['a.id', 'a.title', 'users.name', 'a.published_at', \DB::raw('CASE WHEN r.is_read = "1" THEN 1  ELSE 0 END  as is_read')])
             ->leftJoin('users', 'users.id', '=', 'a.user_id')
             ->orderBy('a.published_at', 'desc')
             ->orderBy('a.id', 'asc')
             ->orderBy('is_read', 'desc')
            ->paginate($limit);
        return $announcements;
    }

    /**
     * Date: 2018/4/23
     * @author Tenny
     * @param $announcements_id
     * @return \Illuminate\Database\Eloquent\Collection|static[]
     */
   public function getAnnouncementDetails($announcements_id, $uid)
   {
       $query = \DB::table('announcements as a');
       $articles = $query->select(['a.id', 'a.title', 'a.content', 'users.name', 'a.published_at'])
           ->leftJoin('users', 'users.id', '=', 'a.user_id')
           ->where('a.id', '=', $announcements_id)
           ->orderBy('a.published_at', 'desc')
           ->orderBy('a.id', 'asc')
           ->first();
       //标记已读 查询已读记录是否存在 不存在增加数据
       $count = \DB::table('announcement_user_pivot')
           ->where('user_id','=', $uid)
           ->where('user_id','=', $announcements_id)
           ->count();
       if($count == false)
       {
           //插入已读记录
           \DB::table('announcement_user_pivot')->insert(
               ['user_id' => $uid, 'announcement_id' => $announcements_id, 'is_read' => 1]
           );
       }else{
           //更新已读操作
           \DB::table('announcement_user_pivot')
               ->where('user_id','=', $uid)
               ->where('user_id','=', $announcements_id)
               ->update([ 'is_read' => 1]);
       }
       return $articles;
   }

    /**
     * Date: 2018/4/23
     * @author Tenny
     * @param $user_id
     * @param $title
     * @param $content
     * @param $project_arr
     * @return \Illuminate\Database\Eloquent\Collection|static[]
     */
    public function createAnnouncement($user_id, $title, $content, $project_arr)
    {
        DB::beginTransaction();
        try{
            $id = DB::table('announcements')->insertGetId(
                ['user_id' => $user_id, 'title' => $title, 'content' => $content]
            );
            if (count($project_arr) == 1){
                if(isset($project_arr[0]) && $project_arr[0] == 0){
                    //全公司
                    DB::table('announcement_project_pivot')->insert(
                        ['announcement_id' => $id, 'type' => 1, 'project_id' => 0]
                    );
                }else{
                    //单个项目
                    DB::table('announcement_project_pivot')->insert(
                        ['announcement_id' => $id, 'type' => 0, 'project_id' => $project_arr[0]]
                    );
                }
            }else{
                $update = [];
                foreach ($project_arr as $velaue){
                    $update[] = [
                        'announcement_id' => $id,
                        'type' => 0,
                        'project_id' => $velaue
                    ];
                }
                DB::table('announcement_project_pivot')->insert($update);
            }
            DB::commit();
        } catch(\Illuminate\Database\QueryException $ex) {
            DB::rollback();
            return false;
        }
        return true;
    }

    /**
     * Date: 2018/4/23
     * @author Tenny
     * @param $user_id
     * @param $title
     * @param $content
     * @param $project_arr
     * @return \Illuminate\Database\Eloquent\Collection|static[]
     */
    public function updateAnnouncement($announcement_id, $title, $content, $project_arr)
    {
        $update = [];
        if($title !== false){
            $update['title'] = $title;
        }
        if($content !== false){
            $update['content'] = $content;
        }
        DB::beginTransaction();
        try{
             DB::table('announcements')
                ->where('id', $announcement_id)
                ->update($update);
             //先删除该文章的所有通知范围
             DB::table('announcement_project_pivot')
                ->where('announcement_id', $announcement_id)
                ->delete();
             if (count($project_arr) == 1){
                if(isset($project_arr[0]) && $project_arr[0] == 0){
                    //全公司
                    DB::table('announcement_project_pivot')->insert(
                        ['announcement_id' => $announcement_id, 'type' => 1, 'project_id' => 0]
                    );
                }else{
                    //单个项目
                    DB::table('announcement_project_pivot')->insert(
                        ['announcement_id' => $announcement_id, 'type' => 0, 'project_id' => $project_arr[0]]
                    );
                }
             }else{
                $update = [];
                foreach ($project_arr as $velaue){
                    $update[] = [
                        'announcement_id' => $announcement_id,
                        'type' => 0,
                        'project_id' => $velaue
                    ];
                }
                if(is_array($update) && count($update) > 0){
                    DB::table('announcement_project_pivot')->insert($update);
                }
             }
             DB::commit();
        } catch(\Illuminate\Database\QueryException $ex) {
            DB::rollback();
            return false;
        }
        return true;
    }

    protected function serialization(array $attributes)
    {
        // TODO: Implement serialization() method.
    }
}